package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.CartDao;
import com.foreknow.elm.po.Business;
import com.foreknow.elm.po.Cart;
import com.foreknow.elm.po.Food;
import com.foreknow.elm.util.DBUtil;
import com.mysql.jdbc.UpdatableResultSet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class CartDaoImpl implements CartDao {
    private Connection conn;
    private PreparedStatement pst;
    @Override
    public List<Cart> listCart(Cart cart) throws SQLException {
        List<Cart> cartList = new ArrayList<>();
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT c.*, ");
        stringBuffer.append("f.foodId ffoodId, ");
        stringBuffer.append("f.foodName ffoodName, ");
        stringBuffer.append("f.foodExplain ffoodExplain, ");
        stringBuffer.append("f.foodImg ffoodImg, ");
        stringBuffer.append("f.foodPrice ffoodPrice, ");
        stringBuffer.append("f.businessId fbusinessId, ");
        stringBuffer.append("b.businessId bbusinessId, ");
        stringBuffer.append("b.businessName bbusinesName, ");
        stringBuffer.append("b.businessAddress bbusinessAddress, ");
        stringBuffer.append("b.businessExplain bbusinessExplain, ");
        stringBuffer.append("b.businessImg bbusinessImg, ");
        stringBuffer.append("b.orderTypeId borderTypeId, ");
        stringBuffer.append("b.starPrice bstarPrice, ");
        stringBuffer.append("b.deliveryPrice bdeliveryPrice, ");
        stringBuffer.append("b.remarks bremarks ");
        stringBuffer.append("FROM cart c left JOIN food f ON c.foodId = f.foodId ");
        stringBuffer.append("LEFT JOIN business b ON c.businessId = b.businessId ");
        stringBuffer.append("WHERE c.userId=? ");
        if(cart.getBusinessId()!=null) {
            stringBuffer.append(" AND b.businessId=" +  cart.getBusinessId());
        }
        try {
            conn = DBUtil.getConnection();
            pst = conn.prepareStatement(stringBuffer.toString());
            pst.setString(1,cart.getUserId());
            ResultSet rs = pst.executeQuery(); //执行查询
            while (rs.next()) {
                Cart cartResult = new Cart();
                cartResult.setCartId(rs.getInt("cartId"));
                cartResult.setFoodId(rs.getInt("foodId"));
                cartResult.setBusinessId(rs.getInt("businessId"));
                cartResult.setUserId(rs.getString("userId"));
                cartResult.setQuantity(rs.getInt("quantity"));
                Food foodResult = new Food();
                foodResult.setFoodId(rs.getInt("ffoodId"));
                foodResult.setFoodName(rs.getString("ffoodName"));
                foodResult.setFoodExplain(rs.getString("ffoodExplain"));
                foodResult.setFoodImg(rs.getString("ffoodImg"));
                foodResult.setFoodPrice(rs.getDouble("ffoodPrice"));
                foodResult.setBusinessId(rs.getInt("businessId"));
                //设置cart与food和business的关联关系
                cartResult.setFood(foodResult);
                Business businessResult = new Business();
                businessResult.setBusinessId(rs.getInt("bbusinessId"));
                businessResult.setBusinessName(rs.getString("bbusinesName"));
                businessResult.setBusinessAddress(rs.getString("bbusinessAddress"));
                businessResult.setBusinessExplain(rs.getString("bbusinessExplain"));
                businessResult.setBusinessImg(rs.getString("bbusinessImg"));
                businessResult.setOrderTypeId(rs.getInt("borderTypeId"));
                businessResult.setStarPrice(rs.getDouble("bstarPrice"));
                businessResult.setDeliveryPrice(rs.getDouble("bdeliveryPrice"));
                businessResult.setRemarks(rs.getString("bremarks"));
                cartResult.setBusiness(businessResult);
                //将Cart对象添加到list集合中
                cartList.add(cartResult);
            }
        }finally {
            DBUtil.close(pst);
        }
        return cartList;
    }

    @Override
    public int saveCart(Cart cart) throws SQLException {
        int result = 0;
        String sql = "insert into cart(foodId,businessId,userId,quantity) values(?,?,?,1)";
        try {
            conn = DBUtil.getConnection();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, cart.getFoodId());
            pst.setInt(2, cart.getBusinessId());
            pst.setString(3, cart.getUserId());
            result = pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return result;
    }

    @Override
    public int updateCart(Cart cart) throws SQLException {
        int result = 0;
        String sql = "UPDATE cart SET quantity=? WHERE userId=? AND businessId=? AND foodId=?";
        try {
            conn = DBUtil.getConnection();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, cart.getQuantity());
            pst.setString(2, cart.getUserId());
            pst.setInt(3, cart.getBusinessId());
            pst.setInt(4, cart.getFoodId());
            result = pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return result;
    }

    @Override
    public int removeCart(Cart cart) throws SQLException {
        StringBuffer sql =
                new StringBuffer("DELETE FROM cart WHERE userId=? AND businessId=?");
        if(cart.getFoodId() != null && cart.getFoodId()>0){
            sql.append(" AND foodId=" + cart.getFoodId());
        }
        int result = 0;
        try {
            conn = DBUtil.getConnection();
            pst = conn.prepareStatement(sql.toString());
            pst.setString(1, cart.getUserId());
            pst.setInt(2, cart.getBusinessId());
            result = pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return result;
    }
}
